SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vw_ResumenPOFDePrivadas
AS
SELECT PP.Descripcion Partida, RS.Descripcion RegimenSalarial, 
    C.Cargo Cargo, 
    CASE RS.Descripcion WHEN 'Cargo' THEN COUNT(*) 
    WHEN 'Horas' THEN SUM(SA.Horas) END AS Cantidad
FROM tb_Cargos C INNER JOIN
    tb_ServiciosAgentes SA ON 
    C.IdCargo = SA.CargoSalarial INNER JOIN
    tb_Organizaciones O ON 
    O.IdOrganizaciones = SA.UbicacionOrig INNER JOIN
    tb_PartidasPresupuestarias PP ON 
    SA.Presupuesto = PP.IdPartida INNER JOIN
    tb_RegimenSalarial RS ON 
    RS.IdRegimenSalarial = C.RegimenSalarial
WHERE O.EsPrivada = 'S' AND TipoDEServicio = 1 AND 
    (SA.RegimenLaboral = 12 OR
    SA.regimenlaboral = 17 OR
    SA.RegimenLaboral = 16) AND (SA.SituacionRevista = 1 OR
    SA.SituacionRevista = 2) AND GetDate() BETWEEN 
    SA.FechaAlta AND ISNULL(Sa.FechaBaja, GetDate())
GROUP BY PP.Descripcion, RS.Descripcion, C.Cargo
GO
GRANT SELECT ON  [dbo].[vw_ResumenPOFDePrivadas] TO [SoloVer]
GO
